﻿using CoreShop.Library;
using CoreShop.Model;
using CoreShop.Repository;
using Microsoft.SqlServer.Server;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Net.Http.Headers;
using System.Reflection;
using System.Runtime.InteropServices.ComTypes;
using System.Security.Policy;
using static CoreShop.Library.GlobalEnumVars;

namespace CoreShop.Services.Services.Report
{
    public class corereportServices : Repository<Reportscorecmsfoodordering>, IcorereportServices
    {
        #region 商品分类统计报表
        /// <summary>
        /// 商品分类统计报表
        /// </summary>
        /// <returns></returns>
        public List<ClassFoodType> GetClassFoodType(long classId)
        {
            List<ClassFoodType> order = new List<ClassFoodType>();
            if (new corecmsfoodcategoryServices().AsQueryable().Where(p=>p.parentId > 0 && p.isDelete == false).Any())
            {
                order = new corecmsfoodordermasterServices().AsQueryable()
                    .LeftJoin<corecmsfoodorderdetail>((a, b) => a.orderId == b.orderId)
                    .LeftJoin<corecmsfood>((a, b, f) => b.foodId == f.id)
                    .LeftJoin<corecmsfoodcategory>((a, b, f, t) => f.foodTypeId == t.id)
                    .LeftJoin<corecmsfoodcategory>((a, b, f, t, g) => t.parentId == g.id)
                    .Where((a, b, f, t, g) => a.classId == classId && b.dataType != (int)GlobalEnumVars.OrderingDataType.Refund)
                    .GroupBy((a, b, f, t, g) => new { g.id, g.name })
                    .Select((a, b, f, t, g) => new ClassFoodType
                    {
                        typeName = g.name,
                        typeQuantity = SqlFunc.AggregateSum(b.quantity),
                        typeAmount = SqlFunc.AggregateSum(b.allAmount),
                    }).With(SqlWith.NoLock).ToList();
            }
            else
            {
                order = new corecmsfoodordermasterServices().AsQueryable()
                    .LeftJoin<corecmsfoodorderdetail>((a, b) => a.orderId == b.orderId)
                    .LeftJoin<corecmsfood>((a, b, f) => b.foodId == f.id)
                    .LeftJoin<corecmsfoodcategory>((a, b, f, t) => f.foodTypeId == t.id)
                    .Where((a, b, f, t) => a.classId == classId && b.dataType != (int)GlobalEnumVars.OrderingDataType.Refund)
                    .GroupBy((a, b, f, t) => new { t.id, t.name })
                    .Select((a, b, f, t) => new ClassFoodType
                    {
                        typeName = t.name,
                        typeQuantity = SqlFunc.AggregateSum(b.quantity),
                        typeAmount = SqlFunc.AggregateSum(b.allAmount),
                    }).With(SqlWith.NoLock).ToList();
            }

            foreach (var item in order)
            {
                if (item.typeQuantity != 0)  item.typePrice = Math.Round(item.typeAmount / item.typeQuantity,2);
            }

            return order;
        }
        #endregion
                
        #region 商品点退赠统计报表
        /// <summary>
        /// 商品点退赠统计
        /// </summary>
        /// <returns></returns>
        public List<Reportscorecmsfoodordering> GetCorecmsfoodorderings(ref int count, int? pageIndex
            , int? pageSize, DateTime? startDate, DateTime? endDate, string name, string typeName)
        {
            var where = Expressionable.Create<corecmsfoodordermaster, corecmsfoodorderdetail, corecmsfood, corecmsfoodcategory>();            

            if (startDate != null)
                where = where.And((a, b, f, t) => a.orderDate >= startDate);

            if (endDate != null)
                where = where.And((a, b, f, t) => a.orderDate <= endDate);

            if (!string.IsNullOrEmpty(typeName))
                where = where.And((a, b, f, t) => t.name.Equals(typeName) || t.helpCode.Equals(typeName));

            if (!string.IsNullOrEmpty(name))
                where = where.And((a, b, f, t) => f.number.Equals(name) || f.helpCode.Equals(name) || f.name.Equals(name));

            List<Reportscorecmsfoodordering> order = new List<Reportscorecmsfoodordering>();
            order = new corecmsfoodordermasterServices().AsQueryable()
                .LeftJoin<corecmsfoodorderdetail>((a, b) => a.orderId == b.orderId)
                .LeftJoin<corecmsfood>((a, b, f) => b.foodId == f.id)
                .LeftJoin<corecmsfoodcategory>((a, b, f, t) => f.foodTypeId == t.id)
                .WhereIF(true, where.ToExpression())
                .GroupBy((a, b, f, t) => new { b.foodId, f.name, foodTypeName = t.name })
                .Select((a, b, f, t) => new Reportscorecmsfoodordering
                {
                    name = f.name,
                    foodTypeName = t.name,
                    quantity = SqlFunc.AggregateSum(b.dataType == 1 ? b.quantity : 0),//点菜数量
                    amount = SqlFunc.AggregateSum(b.dataType == 1 ? b.amount : 0),//点菜金额
                    refQuantity = SqlFunc.AggregateSum(b.dataType == -1 ? b.quantity : 0),//退菜数量
                    refAmount = SqlFunc.AggregateSum(b.dataType == -1 ? b.amount : 0),//退菜金额
                    giveQuantity = SqlFunc.AggregateSum(b.dataType == 0 ? b.quantity : 0),//赠菜数量
                    giveAmount = SqlFunc.AggregateSum(b.dataType == 0 ? b.amount : 0),//赠菜金额
                    saleQuantity = (SqlFunc.AggregateSum(b.dataType == 1 ? b.quantity : 0)) - (SqlFunc.AggregateSum(b.dataType == -1 ? b.quantity : 0)),//销售数量(点菜数量-退菜数量)
                    saleAmount = SqlFunc.AggregateSum(b.dataType == 1 ? b.realAmount : 0) - SqlFunc.AggregateSum(b.dataType == -1 ? b.realAmount : 0),//销售金额(点菜实收金额-退菜实收金额)
                    reduceAmount = (SqlFunc.AggregateSum(b.dataType == 1 ? b.amount : 0)) - (SqlFunc.AggregateSum(b.dataType == -1 ? b.amount : 0)) - (SqlFunc.AggregateSum(b.dataType == 1 ? b.realAmount : 0) - SqlFunc.AggregateSum(b.dataType == -1 ? b.realAmount : 0))//优惠金额（点菜金额-退菜金额-销售金额）
                }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();
            return order;
        }
        #endregion

        #region 支付明细
        /// <summary>
        /// 支付明细
        /// </summary>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sName"></param>
        /// <returns></returns>
        public List<Reportscorecmsfoodorderpayment> GetOrderPaymentDetail(ref int count, int? pageIndex, int? pageSize, DateTime? startDate, DateTime? endDate, int Status, int Payment)
        {
            //var where = Expressionable.Create<corecmsfoodordermaster>();
            var where = Expressionable.Create<corecmsfoodordermaster, corecmsfoodorderpayment, corecmspayments>();
            if (startDate != null)
                where = where.And((a, m, p) => a.orderDate >= startDate);
            if (endDate != null)
                where = where.And((a, m, p) => a.orderDate <= endDate);
            if (Payment > 0)
                where = where.And((a, m, p) => m.paymentId == Payment);
            var list = new corecmsfoodordermasterServices().AsQueryable() 
                  .InnerJoin<corecmsfoodorderpayment>((a, m) => a.orderId == m.orderId)
                  .LeftJoin<corecmspayments>((a, m, p) => m.paymentId == p.id)                  
                  .WhereIF(true, where.ToExpression())                  
                  .Select((a, m, p) => new Reportscorecmsfoodorderpayment
                  {
                      paymentId = m.paymentId,
                      orderId = a.orderId,
                      orderNo = a.orderNo,
                      paymentName = p.name,
                      ordertype = a.ordertype,                      
                      payAmount = m.payAmount,
                      orderDate = a.orderDate,
                  }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count);

            
            return list;           
        }
        #endregion

        #region 支付明细
        /// <summary>
        /// 支付明细
        /// </summary>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sName"></param>
        /// <returns></returns>
        public List<Reportscorecmsfoodorderpayment> GetOrderPaymentSum(ref int count, int? pageIndex, int? pageSize, DateTime? startDate, DateTime? endDate, int Status, int Payment)
        {
            var where = Expressionable.Create<corecmsfoodordermaster>();

            if (startDate != null)
            {
                where = where.And(a => a.orderDate >= startDate);
            }
            if (endDate != null)
            {
                where = where.And(a => a.orderDate <= endDate);
            }
            var list = new corecmsfoodordermasterServices().AsQueryable()
                  .InnerJoin<corecmsfoodorderpayment>((a, m) => a.orderId == m.orderId)
                  .LeftJoin<corecmspayments>((a, m, p) => m.paymentId == p.id)
                  .Where((a, m, p) => Payment > 0 ? m.paymentId == Payment : 1 == 1)
                  .WhereIF(true, where.ToExpression())
                  .GroupBy((a, m, p) => new { p.name, m.paymentId })
                  .Select((a, m, p) => new Reportscorecmsfoodorderpayment
                  {
                      paymentName = p.name,
                      count = SqlFunc.AggregateCount(m.payAmount),
                      refundCount = SqlFunc.AggregateCount(m.payAmount < 0 ? 1 : 0),
                      refundAmount = SqlFunc.AggregateSum(m.payAmount < 0 ? m.payAmount : 0),
                      payAmount = SqlFunc.AggregateSum(m.payAmount),
                  }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();

            var sql = new corecmsfoodordermasterServices().AsQueryable()
                  .InnerJoin<corecmsfoodorderpayment>((a, m) => a.orderId == m.orderId)
                  .LeftJoin<corecmspayments>((a, m, p) => m.paymentId == p.id)
                  .Where((a, m, p) => Payment > 0 ? m.paymentId == Payment : 1 == 1)
                  .WhereIF(true, where.ToExpression())
                  .GroupBy((a, m, p) => new { p.name, m.paymentId })
                  .Select((a, m, p) => new Reportscorecmsfoodorderpayment
                  {
                      paymentName = p.name,
                      count = SqlFunc.AggregateCount(m.payAmount),
                      refundCount = SqlFunc.AggregateCount(m.payAmount < 0 ? 1 : 0),
                      refundAmount = SqlFunc.AggregateSum(m.payAmount < 0 ? m.payAmount : 0),
                      payAmount = SqlFunc.AggregateSum(m.payAmount),
                  }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToSqlValue();

            return list;
        }
        #endregion


        /// <summary>
        /// 商品销售明细
        /// </summary>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <param name="typeName"></param>
        /// <returns></returns>
        public List<corecmsfoodorderdetail> Getcorecmsfoodorder(ref int count, int? pageIndex, int? pageSize, DateTime? startDate, DateTime? endDate, int type, string name, string typeName)
        {
            var where = Expressionable.Create<corecmsfoodordermaster, corecmsfoodorderdetail, corecmsfood, corecmsfoodcategory>();
            where = where.And((a, d, f, c) => a.payStatus == 1 && d.dataType != (int)GlobalEnumVars.OrderingDataType.Refund);
            
            if (startDate != null)
                where = where.And((a, d, f, c) => a.orderDate >= startDate);

            if (endDate != null)
                where = where.And((a, d, f, c) => a.orderDate <= endDate);

            if (!string.IsNullOrEmpty(typeName))
                where = where.And((a, d, f, c) => c.name.Equals(typeName) || c.helpCode.Equals(typeName));

            if (!string.IsNullOrEmpty(name))
                where = where.And((a, d, f, c) => d.number.Equals(name) || d.code.Equals(name) || d.name.Equals(name));

            List<corecmsfoodorderdetail> list = new List<corecmsfoodorderdetail>();
            if (type == 0)
            {
                list = new corecmsfoodordermasterServices().AsQueryable()
                //base.AsQueryable()
                .LeftJoin<corecmsfoodorderdetail>((a, d) => a.orderId == d.orderId)
                .LeftJoin<corecmsfood>((a, d, f) => d.foodId == f.id)
                .InnerJoin<corecmsfoodcategory>((a, d, f, c) => f.foodTypeId == c.id)
                .WhereIF(true, where.ToExpression())
                .GroupBy((a, d, f, c) => new { d.foodId, d.name, d.number, typeName = c.name })
                .Select((a, d, f, c) => new corecmsfoodorderdetail
                {
                    orderNo = "",
                    typeName = c.name,
                    foodId = d.foodId,
                    name = d.name,
                    number = d.number,
                    quantity = SqlFunc.AggregateSum(d.quantity),
                    price = SqlFunc.AggregateSum(d.price),
                    realAmount = SqlFunc.AggregateSum(d.realAmount),
                    reduceAmount = SqlFunc.AggregateSum(d.reduceAmount),
                    costPrice = SqlFunc.AggregateSum(d.costPrice)
                }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();
            }
            else
            {
                list = new corecmsfoodordermasterServices().AsQueryable()
                //base.AsQueryable()
                .LeftJoin<corecmsfoodorderdetail>((a, d) => a.orderId == d.orderId)
                .LeftJoin<corecmsfood>((a, d, f) => d.foodId == f.id)
                .InnerJoin<corecmsfoodcategory>((a, d, f, c) => f.foodTypeId == c.id)
                .Where((a, d, f, c) => f.name.Contains(name) && c.name.Contains(typeName)).WhereIF(true, where.ToExpression())
                .GroupBy((a, d, f, c) => new { d.foodId, d.name, d.number, d.orderId })
                .Select((a, d, f, c) => new corecmsfoodorderdetail
                {
                    orderNo = a.orderNo,
                    orderId = d.orderId,
                    typeName = c.name,
                    foodId = d.foodId,
                    name = d.name,
                    number = d.number,
                    quantity = SqlFunc.AggregateSum(d.quantity),
                    price = SqlFunc.AggregateSum(d.price),
                    realAmount = SqlFunc.AggregateSum(d.realAmount),
                    reduceAmount = SqlFunc.AggregateSum(d.reduceAmount),
                    costPrice = SqlFunc.AggregateSum(d.costPrice)
                }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();
            }
            return list;
        }

        /// <summary>
        /// 商品销售明细
        /// </summary>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <param name="typeName"></param>
        /// <returns></returns>
        public List<ReportOrderDetail> Getcorecmsfoodorderdetail(ref int count, int? pageIndex, int? pageSize, DateTime? startDate, DateTime? endDate, string className, int foodType, string name)
        {
            var where = Expressionable.Create<corecmsfoodordermaster, corecmsfoodorderclass, corecmsstore, corecmsuser, corecmsfoodorderdetail> ();
            where = where.And((a, c, s, u, d) => a.payStatus == 1 && d.dataType != (int)GlobalEnumVars.OrderingDataType.Refund);
            if (startDate != null)
                where = where.And((a, c, s, u, d) => a.orderDate >= startDate);

            if (endDate != null)
                where = where.And((a, c, s, u, d) => a.orderDate <= endDate);

            if (!string.IsNullOrEmpty(className))
                where = where.And((a, c, s, u, d) => a.orderNo.Equals(className) || c.classNumber.Equals(className));

            if (!string.IsNullOrEmpty(name))
                where = where.And((a, c, s, u, d) => d.number.Equals(name) || d.code.Equals(name) || d.name.Equals(name));

            var list = new corecmsfoodordermasterServices().AsQueryable()
             .InnerJoin<corecmsfoodorderclass>((a, c) => a.classId == c.id)
             .InnerJoin<corecmsstore>((a, c, s) => a.storeId == s.id)
             .LeftJoin<corecmsuser>((a, c, s, u) => a.userId == u.id)
             .InnerJoin<corecmsfoodorderdetail>((a, c, s, u, d) => a.orderId == d.orderId)
             .Where((a, c, s, u, d) => c.classNumber.Contains(className) && foodType > 0 ? d.foodTypeId == foodType : 1 == 1).WhereIF(true, where.ToExpression())
             .Select((a, c, s, u, d) => new ReportOrderDetail
             {
                 classNumber = c.classNumber,
                 orderNo = a.orderNo,
                 number = d.number,
                 name = d.name,
                 createTime = a.createTime,
                 quantity = d.quantity,
                 specName = d.specName,
                 price = d.price,
                 discount = d.discount,
                 amount = d.amount,
                 taskAmount = d.taskAmount,
                 feedAmount = d.feedAmount,
                 reduceAmount = d.amount+d.feedAmount+d.taskAmount-d.allAmount,
                 realAmount = d.realAmount,
                 allAmount = d.allAmount,
                 storeName = s.storeName,                 
                 userNumber = u.number,
                 userMobile = u.mobile,
                 parcel = d.parcel,
                 point = 0
             }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();
            return list;
        }

        /// <summary>
        /// 餐段统计
        /// </summary>
        /// <param name="count"></param>
        /// <param name="pageIndex"></param> 
        /// <param name="pageSize"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="sumRow"></param>
        /// <returns></returns>
        public List<Reportscorecmsfoodordermaster> Getcorecmsfoodordermaster(ref int count, int? pageIndex,
            int? pageSize, DateTime? startDate, DateTime? endDate, int marketId, ref Reportscorecmsfoodordermaster sumRow)
        {
            var where = Expressionable.Create<corecmsfoodordermaster>();
            where = where.And(a => a.payStatus == 1);
            if (startDate != null)
            {
                where = where.And(a => a.orderDate >= startDate);
            }
            if (endDate != null)
            {
                where = where.And(a => a.orderDate <= endDate);
            }
            if (marketId > 0)
            {
                var marketIds = new corecmsfoodorderclassServices().AsQueryable()
                    .Where(p => p.marketId == marketId &&
                    ((p.starDate >= startDate && p.starDate <= endDate) || (p.endDate >= startDate && p.endDate <= endDate)))
                    .Select(p => p.id).ToList();
                where = where.And(a => marketIds.Contains(a.classId));
            }
            var list = new corecmsfoodordermasterServices().AsQueryable().WhereIF(true, where.ToExpression())
                .GroupBy((a) => new { a.orderNo, a.orderDate })
                .Select((a) => new Reportscorecmsfoodordermaster
                {
                    orderDate = a.orderDate,
                    sumRealAmount = SqlFunc.AggregateSum(a.sumRealAmount),//营业额=单据实收金额
                    sumReduceAmount = SqlFunc.AggregateSum(a.sumReduceAmount),//优惠金额
                    orderAmount = SqlFunc.AggregateSum(a.sumRealAmount) - SqlFunc.AggregateSum(a.sumReduceAmount),//营业收入
                    orderCount = SqlFunc.AggregateCount(a.orderNo),//订单量
                    strDiscountOrder = SqlFunc.AggregateSum(a.sumRealAmount) / SqlFunc.AggregateCount(a.orderNo),
                    endDiscountOrder = (SqlFunc.AggregateSum(a.sumRealAmount) - SqlFunc.AggregateSum(a.sumReduceAmount)) / SqlFunc.AggregateCount(a.orderNo),
                    sumQuantity = SqlFunc.AggregateSum(a.sumQuantity),
                    strDiscountPeople = SqlFunc.AggregateSum(a.sumRealAmount) / SqlFunc.AggregateSum(a.sumQuantity),
                    endDiscountPeople = (SqlFunc.AggregateSum(a.sumRealAmount) - SqlFunc.AggregateSum(a.sumReduceAmount)) / SqlFunc.AggregateSum(a.sumQuantity),
                }).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();
            sumRow = new corecmsfoodordermasterServices().AsQueryable().WhereIF(true, where.ToExpression())
                .Select((a) => new Reportscorecmsfoodordermaster
                {
                    sumRealAmount = SqlFunc.AggregateSum(a.sumRealAmount),//营业额=单据实收金额
                    sumReduceAmount = SqlFunc.AggregateSum(a.sumReduceAmount),//优惠金额
                    orderAmount = SqlFunc.AggregateSum(a.sumRealAmount) - SqlFunc.AggregateSum(a.sumReduceAmount),//营业收入
                    orderCount = SqlFunc.AggregateCount(a.orderNo),//订单量
                    strDiscountOrder = SqlFunc.AggregateSum(a.sumRealAmount) / SqlFunc.AggregateCount(a.orderNo),
                    endDiscountOrder = (SqlFunc.AggregateSum(a.sumRealAmount) - SqlFunc.AggregateSum(a.sumReduceAmount)) / SqlFunc.AggregateCount(a.orderNo),
                    sumQuantity = SqlFunc.AggregateSum(a.sumQuantity),
                    strDiscountPeople = SqlFunc.AggregateSum(a.sumRealAmount) / SqlFunc.AggregateSum(a.sumQuantity),
                    endDiscountPeople = (SqlFunc.AggregateSum(a.sumRealAmount) - SqlFunc.AggregateSum(a.sumReduceAmount)) / SqlFunc.AggregateSum(a.sumQuantity),
                }).Single();
            return list;
        }

        public List<sysuser> GetUser(ref int count, int mPageIndex, int pageSize, DateTime? startDate, DateTime? endDate, int v1, int v2, ref Reportscorecmsfoodorderpayment sumRow)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 收银员收银统计_收银员对应菜单汇总
        /// </summary>
        /// <param name="id"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public ReportUserMaster GetReportUserMaster(long id, DateTime? startDate, DateTime? endDate)
        {
            var where = Expressionable.Create<corecmsfoodordermaster>();
            where = where.And(a => a.payStatus == 1);
            if (id > 0)
            {
                where = where.And(a => a.sysUserId == id);
            }
            if (startDate != null)
            {
                where = where.And(a => a.orderDate >= startDate);
            }
            if (endDate != null)
            {
                where = where.And(a => a.orderDate <= endDate);
            }
            var list = new corecmsfoodordermasterServices().AsQueryable().WhereIF(true, where.ToExpression())
              //.GroupBy((a) => new { a.orderNo, a.orderDate })
              .GroupBy((a) => new { a.sysUserId })
              .Select((a) => new ReportUserMaster
              {
                  sysUserId = a.sysUserId,
                  sumAmount = SqlFunc.AggregateSum(a.sumAmount),
                  sumRealAmount = SqlFunc.AggregateSum(a.sumRealAmount),
                  sumTaskAmount = SqlFunc.AggregateSum(a.sumTaskAmount),
                  sumFeedAmount = SqlFunc.AggregateSum(a.sumFeedAmount),
                  sumReduceAmount = SqlFunc.AggregateSum(a.sumReduceAmount),
                  sumAllAmount = SqlFunc.AggregateSum(a.sumAllAmount)
              }).Single();
            return list;
        }

        /// <summary>
        /// 收银员对应会员充值金额
        /// </summary>
        /// <param name="id"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public ReportUserBalance GetReportUserBalance(long id, DateTime? startDate, DateTime? endDate)
        {
            var where = Expressionable.Create<corecmsuserbalance>();
            where = where.And(a => a.payStatus == (int)GlobalEnumVars.OrderPayStatus.IsPay);
            if (id > 0)
            {
                where = where.And(a => a.sysUserId == id);
            }
            if (startDate != null)
            {
                where = where.And(a => a.createTime >= startDate);
            }
            if (endDate != null)
            {
                where = where.And(a => a.createTime <= endDate);
            }
            var list = new corecmsuserbalanceServices().AsQueryable().Where(p => p.type == (int)Library.GlobalEnumVars.UserBalanceTypes.Deposit).WhereIF(true, where.ToExpression())
              .GroupBy((a) => a.sysUserId)
              .Select((a) => new ReportUserBalance
              {
                  sysUserId = a.sysUserId,
                  balance = SqlFunc.AggregateSum(a.money) - SqlFunc.AggregateSum(a.giftAmount)
              }).Single();
            return list;
        }

        public List<ReportBalance> Getcorecmsuserbalance(ref int count, int pageIndex, int pageSize, DateTime startDate, DateTime endDate, string txtNumber, ref ReportBalance sumRow)
        {            
            var where = Expressionable.Create<corecmsuserbalance, corecmsuser, sysuser, corecmspayments>();
            where = where.And((a, u, s, m) => a.payStatus != (int)GlobalEnumVars.OrderPayStatus.UnPay);
            if (startDate != null)
                where = where.And((a, u, s, m) => a.createTime >= startDate);
            if (endDate != null)
                where = where.And((a, u, s, m) => a.createTime <= endDate);
            if (!string.IsNullOrEmpty(txtNumber))
                where = where.And((a, u, s, m) => u.userName.Contains(txtNumber) || u.mobile.Contains(txtNumber) || (a.orderId == txtNumber));

            where = where.And((a, u, s, m) => a.status > (int)GlobalEnumVars.BalanceStatus.UnCheck);
            var list = new corecmsuserbalanceServices().AsQueryable()
                .LeftJoin<corecmsuser>((a, u) => a.userId == u.id)
                .LeftJoin<sysuser>((a, u, s) => a.sysUserId == s.id)
                .LeftJoin<corecmspayments>((a, u, s, m) => a.paymentId == m.id)
                .WhereIF(true, where.ToExpression())
                .Select((a, u, s, m) => new ReportBalance
                {
                    id = a.id,
                    orderId = a.orderId,
                    createTime = a.createTime,
                    paymentTime = a.paymentTime,
                    userName = u.userName,
                    mobile = u.mobile,
                    usernumber = u.number,
                    type = a.type,
                    status = a.status,
                    payment = m.name,
                    money =  a.money * a.direction,
                    giftAmount = a.giftAmount,
                    balance = a.balance,
                    giftBalance = a.giftBalance,
                    sysUser = s.userName,
                    memo = a.memo
                }).With(SqlWith.NoLock).OrderBy(a=>a.id,OrderByType.Desc)
                .ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();

            foreach (var item in list)
            {
                item.typeName = EnumHelper.GetDescByValue<GlobalEnumVars.UserBalanceTypes>(item.type);
                item.statusName = EnumHelper.GetDescByValue<GlobalEnumVars.BalanceStatus>(item.status);
            }

            sumRow = new corecmsuserbalanceServices().AsQueryable()
                .LeftJoin<corecmsuser>((a, u) => a.userId == u.id)
                .LeftJoin<sysuser>((a, u, s) => a.sysUserId == s.id)
                .LeftJoin<corecmspayments>((a, u, s, m) => a.paymentId == m.id)
                .WhereIF(true, where.ToExpression())
                .Select((a, u, s, m) => new ReportBalance
                {
                    money = SqlFunc.AggregateSum(a.money * a.direction),
                    giftAmount = SqlFunc.AggregateSum(a.giftAmount * a.direction)
                }).Single();
            return list;
        }
        /// <summary>
        /// 交班报表
        /// </summary>
        /// <param name="count"></param>
        /// <param name="mPageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name=""></param>
        /// <returns></returns>
        public List<ClassDataDto> GetclassOff(ref int count, int pageIndex, int pageSize, DateTime startDate, DateTime endDate, string classNumber)
        {
            var where = Expressionable.Create<corecmsfoodorderclass>();
            if (startDate != null) where = where.And(c => c.createTime >= startDate);
            if (endDate != null) where = where.And(c => c.createTime <= endDate);

            List<ClassDataDto> classList = new corecmsfoodorderclassServices().AsQueryable()
             .LeftJoin<corecmsfoodordermaster>((c, o) => c.id == o.classId)
             .LeftJoin<corecmsstoremarket>((c, o, m) => c.marketId == m.id)
             .LeftJoin<sysuser>((c, o, m, s) => c.sysUserId == s.id)
             .LeftJoin<corecmsfoodorderdetail>((c, o, m, s, d) => o.orderId == d.orderId)
             .Where((c, o, m, s, d) => c.classNumber.Contains(classNumber) && d.dataType != (int)GlobalEnumVars.OrderingDataType.Refund )
             .WhereIF(true, where.ToExpression())
             .GroupBy((c, o, m, s, d) => new { c.id, o.classId })
             .Select((c, o, m, s, d) => new ClassDataDto
             {
                 classId = o.classId,
                 userName = s.userName,
                 classNumber = c.classNumber,
                 starDate = SqlFunc.AggregateMin(o.orderDate),
                 endDate = SqlFunc.AggregateMax(o.orderDate),
                 marketName = m.name,
                 allCount = SqlFunc.AggregateCount(o.orderNo),
                 allPerson = SqlFunc.AggregateSum(o.person),
                 allFoodCount = SqlFunc.AggregateSum(d.quantity),
                 refundFoodCount = 0,
                 sumShouldAmount = SqlFunc.AggregateSum(o.sumAmount),
                 sumRealAmount = SqlFunc.AggregateSum(o.sumRealAmount),
                 sumAllAmount = SqlFunc.AggregateSum(o.sumAllAmount),
                 sunReduceAmount = SqlFunc.AggregateSum(d.reduceAmount)
             }).With(SqlWith.NoLock).ToPageList((int)pageIndex, (int)pageSize, ref count).ToList();

            var classIds  = classList.Select(p=>p.classId).ToList();

            //券消费
            List<Consumption> ConsumptionList = new corecmsfoodorderclassServices().AsQueryable()
                .LeftJoin<corecmsfoodordermaster>((c, m) => m.classId == c.id)
                .LeftJoin<corecmsfoodorderpayment>((c, m, y) => m.orderId == y.orderId)
                .LeftJoin<corecmspayments>((c, m, y, s) => s.id == y.paymentId)
                .Where((c, m, y, s) => classIds.Contains(c.id))
                .GroupBy((c, m, y, s) => new { s.id, m.classId })
                .Select((c, m, y, s) => new Consumption
                {
                    classId = m.classId,
                    payMentName = s.name,
                    Amount = SqlFunc.AggregateSum(y.takeAmount),
                }).ToList();

            //会员充值
            List<userDeposit> userRechargeList = new corecmsfoodorderclassServices().AsQueryable()
                .LeftJoin<corecmsuserbalance>((c, b) => c.id == b.classId)
                .LeftJoin<corecmspayments>((c, b, s) => s.id == b.paymentId)
                .Where((c, b, s) => classIds.Contains(c.id) && b.type == (int)GlobalEnumVars.UserBalanceTypes.Deposit)
                .GroupBy((c, b, s) => s.id).GroupBy((c, b, s) => c.id)
                .Select((c, b, s) => new userDeposit
                {
                    classId = c.id,
                    payMentName = s.name,
                    Amount = SqlFunc.AggregateSum(b.money) - SqlFunc.AggregateSum(b.giftAmount),
                    giftAmount = SqlFunc.AggregateSum(b.giftAmount)
                }).ToList();

            //退菜统计
            var refunds = new corecmsfoodorderclassServices().AsQueryable()
                    .LeftJoin<corecmsfoodordermaster>((c, o) => c.id == o.classId)
                    .LeftJoin<corecmsfoodorderdetail>((c, o, d) => o.orderId == d.orderId)
                    .Where((c, o, d) => classIds.Contains(c.id) && d.dataType == (int)GlobalEnumVars.OrderingDataType.Refund)
                    .WhereIF(true, where.ToExpression())
                    .GroupBy((c, o, d) => c.id)            
                    .Select((c, o, d) => new Consumption
                    {
                        classId = c.id,
                        Amount = SqlFunc.AggregateSum(d.quantity)
                    }).ToList();

            foreach (ClassDataDto item in classList)
            {
                item.Consumption = ConsumptionList.Where(p => p.classId == item.classId).ToList();
                item.sum_posAmount = item.Consumption.Sum(p => p.Amount);
                item.userDeposits = userRechargeList.Where(p => p.classId == item.classId).ToList();
                item.sum_depAmount = item.userDeposits.Sum(p => p.Amount);
                item.refundFoodCount = refunds.Where(p => p.classId == item.classId).Sum(p => p.Amount);
                item.sum_allAmount = item.sum_posAmount + item.sum_depAmount;
            }
            return classList;
        }
    }
}